-- 网点滞留件明细 （回刷周期20天）
-- 开发：侯文龙
-- 日期：2023-02-06
insert overwrite table jms_dm.dm_network_retention_detail_dt partition (dt)
-- 网点滞留件明细
select
       end_center_send_time    --末端重心发件时间
       ,date_format(final_plan_sign_time ,'yyyy-MM-dd') as final_plan_sign_date
       ,final_plan_sign_time  --规划签收时间
       ,case when aging_sign_time is null and is_difficult = 1 then 25
            when aging_sign_time is null then 26
            when aging_sign_time <= final_plan_sign_time and aging_sign_scan_type = '快件签收' then 11
            when aging_sign_time <= final_plan_sign_time and aging_sign_scan_type = '代理点收入扫描' then 14
            when aging_sign_time <= final_plan_sign_time and is_aging_sign_pdd = 1 then 12
            when aging_sign_time <= final_plan_sign_time and is_aging_sign_pdd = 2 then 13
            when aging_sign_time > final_plan_sign_time and aging_sign_scan_type = '快件签收' then 21
            when aging_sign_time > final_plan_sign_time and aging_sign_scan_type = '代理点收入扫描' then 24
            when aging_sign_time > final_plan_sign_time and is_aging_sign_pdd = 1 then 22
            when aging_sign_time > final_plan_sign_time and is_aging_sign_pdd = 2 then 23
            else 26 end as aging_sign_count_type
       ,billcode -- 运单号
       ,final_sign_network_code  -- 派件网点编码
       ,final_sign_network_name  -- 派件网点名称
       ,end_center_code  -- 转运中心/集散编码
       ,end_center_name  -- 末端中心/集散名称
       ,end_manage_region_code  -- 管理大区名称
       ,end_manage_region_name  -- 管理大区编码
       ,end_agent_code  -- 代理区编码
       ,end_agent_name  -- 代理区
       ,end_franchisee_code  -- 加盟商编码
       ,end_franchisee_name  -- 加盟商
       ,end_provider_id  -- 省份id
       ,end_provider_name  -- 省份
       ,end_city_id  -- 城市id
       ,end_city_name  -- 城市
       ,end_area_id -- 区县id
       ,end_area_name -- 区县
       ,end_network_arrival_time -- 网点到件时间
       ,deliver_time -- 网点出仓时间
       ,collect_in_time -- 时效签收时间
       ,difficult_type  -- 问题件类型
       ,terminal_type  -- 对接类型
       ,deliver_out_time  -- 驿站/快递柜业务员取出时间
       ,collect_out_time  -- 出库时间
       ,sign_time  -- 签收时间
       ,proxy_in_time  -- 代理点收入时间
       ,proxy_in_network_name  -- 代理点名称
       ,difficult_time  -- 问题件扫描时间
       ,deliver_township_name -- 出仓乡镇
       ,deliver_shift -- 散货班次
       ,goods_type_code -- 物品类型
       ,order_source_name -- 订单来源
       ,order_source_code --订单来源编码
       ,to_date(final_plan_sign_time)  as date_time
       ,third_code
       ,third_name  --三段码名字
       ,to_date(final_plan_sign_time) as dt    -- 分区日期
from jms_dwd.dwd_terminal_sign_all_detail_new_dt t1
left anti join (
    select waybill_no,max(scan_time) as last_scan_time
    from jms_dwd.dwd_tab_barscan_arrival_base_dt
    where dt between date_add('{{ execution_date | cst_ds }}', -29) and date_add('{{ execution_date | cst_ds }}', 0)
  group by waybill_no
    ) t2
on t1.billcode=t2.waybill_no
and nvl(t1.print_time,'2020-01-01 00:00:00')>end_center_send_time
and t2.last_scan_time>=nvl(t1.print_time,'2049-12-31 23:59:59')
where dt between date_format(date_add('{{ execution_date | cst_ds }}', -29), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', 0), 'yyyy-MM-dd')
  and to_date(final_plan_sign_time) between date_format(date_add('{{ execution_date | cst_ds }}', -28), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', 0), 'yyyy-MM-dd')
  and is_reject_reback_transfer = 2
  and is_end_piece = 2
  and end_center_send_time <= nvl(actual_sign_time,'9999-99-99')
  and aging_sign_time is null
distribute by dt, abs(hash(billcode)) % 2
  ;

insert overwrite table jms_dm.dm_network_retention_detail_real_time_dt partition (dt)
select
 end_center_send_time
 ,final_plan_sign_date
 ,final_plan_sign_time
 ,aging_sign_count_type
 ,billcode
 ,final_sign_network_code
 ,final_sign_network_name
 ,end_center_code
 ,end_center_name
 ,end_manage_region_code
 ,end_manage_region_name
 ,end_agent_code
 ,end_agent_name
 ,end_franchisee_code
 ,end_franchisee_name
 ,end_provider_id
 ,end_provider_name
 ,end_city_id
 ,end_city_name
 ,end_area_id
 ,end_area_name
 ,end_network_arrival_time
 ,deliver_time
 ,collect_in_time
 ,difficult_type
 ,terminal_type
 ,deliver_out_time
 ,collect_out_time
 ,sign_time
 ,proxy_in_time
 ,proxy_in_network_name
 ,difficult_time
 ,deliver_township_name
 ,deliver_shift
 ,goods_type_code
 ,order_source_name
 ,order_source_code
 ,date_time
 ,third_code
 ,'{{ execution_date | cst_ds }}' as dt
from jms_dm.dm_network_retention_detail_dt
where dt  between date_add('{{ execution_date | cst_ds }}', -28) and date_add('{{ execution_date | cst_ds }}', 0)
distribute by dt, abs(hash(billcode)) % 5
  ;